<?php
class ReportesController extends AppController {

	var $name = 'Reportes';
	var $uses = array('User','PeoplePractice','PeopleProduct','Evolution','InsurancesPerson');

   function beforeFilter () {
        parent::beforeFilter();

		// Aplico el Layout segun la Vista o si es AJAX
		if ($this->RequestHandler->isAjax()) {
			$this->layout = 'ajax';
		} else {
			// Cambio la Vista por la del Rol
			$this->layout = strtolower($this->Session->read('VistaActual'));
		}
    }



	function index() { 	

		
		$reportes[1] = __('Reporte económico', true);
		$reportes[2] = __('Pacientes atendidos', true);
		$reportes[3] = __('Tiempo de demora en sala de espera', true);
		//$reportes[4] = __('Auditoría de Calidad Médica (prox.)', true);
		$reportes[5] = __('Reporte de problemas comunes', true);
		$this->set('reportes',$reportes);
		$tipo_reporte = 0;
		$busqueda_realizada = 0;
	
		//-----------------------------------------------------------------------------------
		//                                                  Problemas Atendidos por el medico
		//-----------------------------------------------------------------------------------
		$user_id = $this->Auth->user('id');
		$client_id = $this->Session->read('ClientesUsuarioLogueado.id');
		$this->loadModel('PeopleProblem');
		
		$sql = "select pp.problem_id, p.name
				from people_problems as pp
				left join problems as p on pp.problem_id=p.id
				where pp.client_id='$client_id'
					and pp.user_id='$user_id'
					and pp.ficha_id=0 and pp.staterecord_id='1'
				group by pp.problem_id
				order by p.name ASC";
		$ProblemasAtendidos = $this->PeopleProblem->query( $sql);
		$this->set('ProblemasAtendidos',$ProblemasAtendidos);
		//pr($ProblemasAtendidos);
	
		if($_POST){
		
			$busqueda_realizada = 1;
		
			$tipo_reporte = $this->data['Reportes']['tipo_reporte'];
			
			list($d_dia,$d_mes,$d_ano) = explode('/',$this->data['Reportes']['desde'] );
			$xdesde = mktime(0,0,0,$d_mes,$d_dia,$d_ano);
			$desde = date("Y-m-d H:i:s",$xdesde);


			list($h_dia,$h_mes,$h_ano) = explode('/',$this->data['Reportes']['hasta'] );
			$xhasta = mktime(23,59,59,$h_mes,$h_dia,$h_ano);
			$hasta = date("Y-m-d H:i:s",$xhasta);

			$client_id = $this->Session->read('ClientesUsuarioLogueado.id');
			$user_id   = $this->Session->read('Auth.User.id');


			switch ($tipo_reporte) {
				case 1: //--------------------------------------Reporte Económico
					
					$Productos = $this->PeopleProduct->find('all', array(
												'conditions' => array(
													'PeopleProduct.client_id' => $client_id,
													'PeopleProduct.user_id' => $user_id,
													'PeopleProduct.created >= ' => $desde,
													'PeopleProduct.created <= ' => $hasta) , 
												'order' => array(
													'PeopleProduct.insurance_id' => 'ASC',
													'PeopleProduct.plan' => 'ASC')
												));
					$this->set('Resultado',$Productos);												
//pr($Productos);
					break;
					
				case 2: //-------------------------------------Pacientes Atendidos
				
					//-----------------------------------------------------------------------------------
					//                                                                        Evoluciones
					//-----------------------------------------------------------------------------------
					$this->Evolution->unbindModel( array('belongsTo' => array('Client', 'Problem') ) );
					$x = $this->Evolution->find('all', array(
												'conditions' => array(
													'Evolution.client_id' => $client_id,
													'Evolution.fecha >= ' => $desde,
													'Evolution.fecha <= ' => $hasta, 
													'Evolution.staterecord_id' => 1) , 
												'order' => array('Evolution.fecha' => 'ASC'),
												));


					$Evoluciones = array();												
					if ($x){
						foreach($x as $z){
						
							$y['id']          = $z['Evolution']['id'];
							$y['person_id']   = $z['Evolution']['person_id'];
							$y['fecha']       = $z['Evolution']['fecha'];
							$y['paciente']    = $z['Person']['full_name'];
							$y['medico_id']   = $z['Evolution']['user_id'];
							$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
							$y['origen']      = 'Evolucion';
							
							$Evoluciones[] = $y;
						}
						unset($x);
					}								


					//-----------------------------------------------------------------------------------
					//                                                                        Problemas
					//-----------------------------------------------------------------------------------
					$this->loadModel('PeopleProblem');
					$this->PeopleProblem->unbindModel( array('belongsTo' => array('Client', 'Problem', 'Stateproblem','Tag','Staterecord') ) );
					$x = $this->PeopleProblem->find('all', array(
												'conditions' => array(
														'PeopleProblem.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
														'PeopleProblem.fecha_origen >= ' => $desde,
														'PeopleProblem.fecha_origen <= ' => $hasta, 
														'PeopleProblem.ficha_id' => 0,
														'PeopleProblem.staterecord_id' => 1
														),
												'order' => array( 'PeopleProblem.fecha_origen' => 'ASC'),
											) );
					
					$Problemas = array();												
					if ($x){
						foreach($x as $z){
							$y['id']          = $z['PeopleProblem']['id'];
							$y['person_id']   = $z['PeopleProblem']['person_id'];
							$y['fecha']       = $z['PeopleProblem']['fecha_origen'];
							$y['paciente']    = $z['Person']['full_name'];
							$y['medico_id']   = $z['PeopleProblem']['user_id'];
							$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
							$y['origen']      = 'Problemas';
							
							$Problemas[] = $y;
						}
						unset($x);
					}								

					//-----------------------------------------------------------------------------------
					//                                                                        Practicas
					//-----------------------------------------------------------------------------------
					$this->loadModel('PeoplePractice');
					$this->PeoplePractice->unbindModel( array('belongsTo' => array('Client', 'Practice', 'Staterecord') ) );
					$x = $this->PeoplePractice->find('all', array(
												'conditions' => array(
														'PeoplePractice.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
														'PeoplePractice.fecha >= ' => $desde,
														'PeoplePractice.fecha <= ' => $hasta, 
														'PeoplePractice.ficha_id' => 0,
														'PeoplePractice.staterecord_id' => 1
														),
												'order' => array( 'PeoplePractice.fecha' => 'ASC'),
												'group' => array( 'PeoplePractice.fecha','PeoplePractice.person_id'),																							
											) );
					$Practicas = array();												
					if ($x){
						foreach($x as $z){
							$y['id']          = $z['PeoplePractice']['id'];
							$y['person_id']   = $z['PeoplePractice']['person_id'];
							$y['fecha']       = $z['PeoplePractice']['fecha'];
							$y['paciente']    = $z['Person']['full_name'];
							$y['medico_id']   = $z['PeoplePractice']['user_id'];
							$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
							$y['origen']      = 'Practicas';
							
							$Practicas[] = $y;
						}
						unset($x);
					}								
					
					//-----------------------------------------------------------------------------------
					//                                                                    Signos Vitales
					//-----------------------------------------------------------------------------------
					$this->loadModel('PeopleSign');
					$this->PeopleSign->unbindModel( array('belongsTo' => array('Client', 'Sign', 'Staterecord') ) );
					$x = $this->PeopleSign->find('all', array(
												'conditions' => array(
														'PeopleSign.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
														'PeopleSign.fecha >= ' => $desde,
														'PeopleSign.fecha <= ' => $hasta, 
														'PeopleSign.ficha_id' => 0,
														'PeopleSign.staterecord_id' => 1
														),
												'order' => array( 'PeopleSign.fecha' => 'ASC'),
												'group' => array( 'PeopleSign.fecha','PeopleSign.person_id'),
											) );
									
					$Signos = array();												
					if ($x){
						foreach($x as $z){
							$y['id']          = $z['PeopleSign']['id'];
							$y['person_id']   = $z['PeopleSign']['person_id'];
							$y['fecha']       = $z['PeopleSign']['fecha'];
							$y['paciente']    = $z['Person']['full_name'];
							$y['medico_id']   = $z['PeopleSign']['user_id'];
							$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
							$y['origen']      = 'SignosVitales';
							
							$Signos[] = $y;
						}
						unset($x);
					}								

					//-----------------------------------------------------------------------------------
					//                                                                    Medicamentos
					//-----------------------------------------------------------------------------------
					$this->loadModel('PeopleVademecum');
					$this->PeopleVademecum->unbindModel( array('belongsTo' => array('Client', 'Vademecum', 'Typeindicacionmedicamento','Staterecord') ) );
					$x = $this->PeopleVademecum->find('all', array(
												'conditions' => array(
														'PeopleVademecum.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
														'PeopleVademecum.created >= ' => $desde,
														'PeopleVademecum.created <= ' => $hasta, 
														'PeopleVademecum.ficha_id' => 0,
														'PeopleVademecum.staterecord_id' => 1
														),
												'order' => array( 'PeopleVademecum.created' => 'ASC'),
											) );
							
					$Medicamentos = array();												
					if ($x){
						foreach($x as $z){
							$y['id']          = $z['PeopleVademecum']['id'];
							$y['person_id']   = $z['PeopleVademecum']['person_id'];
							$y['fecha']       = $z['PeopleVademecum']['created'];
							$y['paciente']    = $z['Person']['full_name'];
							$y['medico_id']   = $z['PeopleVademecum']['user_id'];
							$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
							$y['origen']      = 'Medicamentos';
							
							$Medicamentos[] = $y;
						}
						unset($x);
					}								

					//-----------------------------------------------------------------------------------
					//                                                                    Fichas
					//-----------------------------------------------------------------------------------
					$this->loadModel('Ficha');
					$this->Ficha->unbindModel( array('belongsTo' => array('Client', 'Formulario' ) ) );
					$x = $this->Ficha->find('all', array(
												'conditions' => array(
														'Ficha.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
														'Ficha.created >= ' => $desde,
														'Ficha.created <= ' => $hasta, 
														'Ficha.staterecord_id' => 1
														),
												'order' => array( 'Ficha.created' => 'ASC'),
											) );

					$Fichas = array();												
					if ($x){
						foreach($x as $z){
							$y['id']          = $z['Ficha']['id'];
							$y['person_id']   = $z['Ficha']['person_id'];
							$y['fecha']       = $z['Ficha']['created'];
							$y['paciente']    = $z['Person']['full_name'];
							$y['medico_id']   = $z['Ficha']['user_id'];
							$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
							$y['origen']      = 'Fichas';
							
							$Fichas[] = $y;
						}
						unset($x);
					}								
					
					
					//-----------------------------------------------------------------------------------
					//                                                            Arma el array de salida
					//-----------------------------------------------------------------------------------
					$Todos = array_merge($Evoluciones, $Problemas, $Practicas, $Signos, $Medicamentos, $Fichas);
					$Resultado = $this->ordenar_array($Todos, 'fecha', SORT_ASC,'person_id', SORT_ASC); 
					
					foreach($Resultado as $clave=>$valor){
						//-------------------------------------------------- 
						//            Obtiene las Insurances de ese Paciente					
						$person_id = $valor['person_id'];
						$Insurance = $this->InsurancesPerson->find('all', array(
											'conditions' => array('InsurancesPerson.person_id' => $person_id)
											));
						$financiador = '';
						if($Insurance){
							foreach($Insurance as $ii){
								$financiador.= $ii['Insurance']['name'].',';
							}
						}
						$Resultado[$clave]['financiador'] = $financiador;
					}
					//pr($Resultado);
					$this->set('Resultado',$Resultado);
					
					
					break;
				case 3: //-------------------------------------Gestión de Pacientes con Turnos (prox.)

					$Resultado = $this->PeopleProduct->query(
						"SELECT  t2.id AS Paciente_id,CONCAT(t2.name, ' ', t2.name2, ' ',t2.lastname, ' ',t2.lastname2, ' ',t2.lastname3) AS Paciente, (YEAR(CURDATE()) - YEAR(t2.fecha_nacimiento)) as Edad, Turn.fecha AS fecha_turno, CONCAT(t3.name, ' ', t3.lastname) AS Profesional, Turn.hora_llegada, Turn.hora_atendido, round((UNIX_TIMESTAMP(Turn.hora_atendido) - UNIX_TIMESTAMP(Turn.hora_llegada))/60) AS min_espera, round((UNIX_TIMESTAMP(Turn.hora_atendido) - UNIX_TIMESTAMP(Turn.fecha))/60) AS min_espera_turno
						FROM
						turns AS Turn
						INNER JOIN people AS t2 ON t2.id = Turn.person_id
						INNER JOIN users AS t3 on t3.id = Turn.doctor_id
						WHERE
						Turn.client_id = '".$this->Session->read('ClientesUsuarioLogueado.id')."' AND Turn.fecha >= '".$desde."' AND Turn.fecha <= '".$hasta."' AND Turn.hora_atendido is not null AND Turn.hora_atendido <> '0000-00-00 00:00:00' 
						AND
							(
								(Turn.hora_llegada is not null AND Turn.hora_llegada <> '0000-00-00 00:00:00') OR
								(Turn.fecha is not null AND Turn.fecha <> '0000-00-00 00:00:00')
							);
						"
					);
					$this->set('Resultado',$Resultado);												

					break;
				case 4: //-------------------------------------Auditoría de Calidad Médica (prox.)
					//echo "i es igual a 2";
					break;
					
				case 5: //-------------------------------------Reporte de Problemas Comunes
					$problema_id = $this->data['Reportes']['problema'];
					$this->set('problema_id',$problema_id);
					$sql = "select pp.id, pp.user_id, pp.problem_id, pp.fecha_origen, pp.person_id, p.name, pe.name, pe.lastname, 
								u.name as med_name, u.lastname as med_lastname
							from people_problems as pp
								left join problems as p on pp.problem_id=p.id
								left join people as pe on pp.person_id=pe.id
								left join users as u on pp.user_id=u.id
							where pp.client_id='$client_id'
								and pp.ficha_id=0 and pp.staterecord_id='1'
								and pp.problem_id = '$problema_id'
								and pp.fecha_origen>='$desde' and pp.fecha_origen<='$hasta'
							order by pp.fecha_origen DESC";
					
					$Resultado = $this->PeopleProblem->query( $sql);
					//pr($Resultado); die();
					$this->set('Resultado',$Resultado);												

					break;
					
			}			

		} else {

			$Productos = array();
			$xdesde    = mktime(0,0,0,date('m'),1,date('Y'));			
			$xhasta    = time();						
			$tipo_reporte = 1;
		}


		
		$this->set('xdesde',$xdesde);
		$this->set('xhasta',$xhasta);
		$this->set('tipo_reporte',$tipo_reporte);
		$this->set('busqueda_realizada',$busqueda_realizada);		
	} 


	function ordenar_array() {
		/* 
		// EJEMPLO DE USO: 
		// Otra manera de declarar un array bidimensional de estos... 
		$array_a_ordenar = array( 
						 0 => array('campo1' => 'patatas', 'campo2' => 1, 'campo3' => 'kkkk'), 
						 1 => array('campo1' => 'coles', 'campo2' => 3, 'campo3' => 'aaaa'), 
						 2 => array('campo1' => 'tomates', 'campo2' => 1, 'campo3' => 'zzzz'), 
						 3 => array('campo1' => 'peras', 'campo2' => 7, 'campo3' => 'hhhh'), 
						 4 => array('campo1' => 'tomates', 'campo2' => 4, 'campo3' => 'bbbb'), 
						 5 => array('campo1' => 'aguacates', 'campo2' => 3, 'campo3' => 'yyyy'), 
				 ); 
		 
		$array_ordenadito = ordenar_array($array_a_ordenar, 'campo2', SORT_DESC, 'campo1', SORT_DESC) or die('<br>ERROR!<br>'); 
		$array_ordenadito2 = ordenar_array($array_a_ordenar, 'campo3', SORT_DESC, 'campo2', SORT_DESC, 'campo1', SORT_ASC ) or die('<br>ERROR!<br>');  
		*/
			
		  $n_parametros = func_num_args(); // Obenemos el número de parámetros 
		  if ($n_parametros<3 || $n_parametros%2!=1) { // Si tenemos el número de parametro mal... 
			return false; 
		  } else { // Hasta aquí todo correcto...veamos si los parámetros tienen lo que debe ser... 
			$arg_list = func_get_args(); 
		 
			if (!(is_array($arg_list[0]) && is_array(current($arg_list[0])))) { 
			  return false; // Si el primero no es un array...MALO! 
			} 
			for ($i = 1; $i<$n_parametros; $i++) { // Miramos que el resto de parámetros tb estén bien... 
			  if ($i%2!=0) {// Parámetro impar...tiene que ser un campo del array... 
				if (!array_key_exists($arg_list[$i], current($arg_list[0]))) { 
				  return false; 
				} 
			  } else { // Par, no falla...si no es SORT_ASC o SORT_DESC...a la calle! 
				if ($arg_list[$i]!=SORT_ASC && $arg_list[$i]!=SORT_DESC) { 
				  return false; 
				} 
			  } 
			} 
			$array_salida = $arg_list[0]; 
		 
			// Una vez los parámetros se que están bien, procederé a ordenar... 
			$a_evaluar = "foreach (\$array_salida as \$fila){\n"; 
			for ($i=1; $i<$n_parametros; $i+=2) { // Ahora por cada columna... 
			  $a_evaluar .= "  \$campo{$i}[] = \$fila['$arg_list[$i]'];\n"; 
			} 
			$a_evaluar .= "}\n"; 
			$a_evaluar .= "array_multisort(\n"; 
			for ($i=1; $i<$n_parametros; $i+=2) { // Ahora por cada elemento... 
			  $a_evaluar .= "  \$campo{$i}, SORT_REGULAR, \$arg_list[".($i+1)."],\n"; 
			} 
			$a_evaluar .= "  \$array_salida);"; 
			// La verdad es que es más complicado de lo que creía en principio... :) 
		 
			eval($a_evaluar); 
			return $array_salida; 
		  } 
	} // end function ordenar_array
	
	


	function exportar1($xdesde=0,$xhasta=0) { 	
        $this->layout = '';
		$title = 'Reporte económico';

		if($xdesde == 0) { $xdesde = time()-(60*60*24*30); }
		if($xhasta == 0) { $xhasta = time();}
		
		$desde = date("Y-m-d H:i:s",$xdesde);
		$hasta = date("Y-m-d H:i:s",$xhasta);
		$cdesde = date("Y-m-d",$xdesde);
		$chasta = date("Y-m-d",$xhasta);

		$client_id = $this->Session->read('ClientesUsuarioLogueado.id');
		$user_id   = $this->Session->read('Auth.User.id');

		
		$Productos = $this->PeopleProduct->find('all', array(
										'conditions' => array(
												'PeopleProduct.client_id' => $client_id,
												'PeopleProduct.user_id' => $user_id,
												'PeopleProduct.created >= ' => $desde,
												'PeopleProduct.created <= ' => $hasta) , 
										'order' => array(
											'PeopleProduct.insurance_id' => 'ASC',
											'PeopleProduct.plan' => 'ASC')
										));

		$nombre_temporal = 'Reporte-Economico '.$cdesde.' al '.$chasta.'.xls';
		$destino = ROOT.DS.'app'.DS.'webroot'.DS.'temp/'.$nombre_temporal;		

		$fp = fopen("$destino", 'w');


		$reng = "<table cellpadding='0' cellspacing='0' id='reporte_1'>
			<thead>
				<tr>	
					<th>Fecha</th>
					<th>Paciente</th>
					<th>Financiador</th>
					<th>Plan</th>
					<th>Práctica</th>
					<th>Importe</th>
				</tr>
			</thead>";
			
		fwrite($fp, $reng);

		$total_practica    = $c_practicas   = 0; $product_name = '';
		$total_plan        = $c_plan        = 0; $plan_name    = '';
		$total_financiador = $c_financiador = 0; $financiador  = '';
		$total_fecha       = $c_fecha       = 0; $fecha        = '';
		$total_general     = $c_total       = 0;
		$financiador_id = 0;

		foreach($Productos as $p){  

			$total_general = $total_general + $p['PeopleProduct']['importe'];
			$c_total++;

					
			if ( empty($plan_name) or $financiador_id==0 ) { 
				$plan_name      = $p['PeopleProduct']['plan']; 
				$financiador    = $p['Insurance']['name']; 
				$financiador_id = $p['PeopleProduct']['insurance_id']; 
			} 

					
			if($plan_name == $p['PeopleProduct']['plan'] ) {
				$total_plan = $total_plan + $p['PeopleProduct']['importe'];
				$c_plan++;
			} else { // Muestro el Total del Plan

				$reng = "<tr style='background:#fff;'><td style='text-align:right;' colspan='6'>
						<b>Plan $plan_name &nbsp;&nbsp;[Cant: $c_plan]&nbsp;&nbsp;$".number_format($total_plan,2)."</b></td></tr>";
				fwrite($fp, $reng);						
				$total_plan = $p['PeopleProduct']['importe'];
				$plan_name  = $p['PeopleProduct']['plan']; 
				$c_plan = 1;	
			}	
					
					
			if($financiador_id == $p['PeopleProduct']['insurance_id'] ) {
				$total_financiador = $total_financiador + $p['PeopleProduct']['importe'];
				$c_financiador++;

			} else { // Muestro el Total del Financiador
				$reng = '<tr style="background:#efefef;"><td style="text-align:right;" colspan="6">';
				$reng.= '<b>Total Financiador '.$financiador.' &nbsp;&nbsp;[Cant: '.$c_financiador.']&nbsp;&nbsp;$'.number_format($total_financiador,2).'</b></td></tr>';
				fwrite($fp, $reng);				
				$total_financiador = $p['PeopleProduct']['importe'];
				$financiador    = $p['Insurance']['name']; 
				$financiador_id = $p['PeopleProduct']['insurance_id']; 
				$c_financiador = 1;	
				
			}
					
			$x = substr($p['PeopleProduct']['created'],0,10);
			list($ano,$mes,$dia) = explode('-',$x);
			$fecha = "$dia/$mes/$ano";
			$reng = "<tr style='background:#FFFFFF;'>";
			$reng.=  "<td style='text-align:left;'>$fecha</td>";

			$reng.= '<td>'.$p['Person']['full_name'].'</td>';

			$reng.= "<td>$financiador</td>";
			$reng.= "<td>$plan_name</td>";
			$reng.= "<td>{$p['PeopleProduct']['product_name']}</td>";
			$reng.= "<td style='text-align:right;'>$".number_format($p['PeopleProduct']['importe'],2)."</td>";
			$reng.= "</tr>";
			fwrite($fp, $reng);		
		} //endforeach 

		// Muestro el Total del Ultimo Plan
		$reng = "<tr style='background:#fff;'><td style='text-align:right;' colspan='6'>";
		$reng.= "<b>Plan $plan_name &nbsp;&nbsp;[Cant: $c_plan]&nbsp;&nbsp;$".number_format($total_plan,2)."</b></td>";
		$reng.= "</tr>";
			
		// Muestro el Total del Ultimo Financiador
		$reng.= "<tr style='background:#efefef;'><td style='text-align:right;' colspan='6'>";
		$reng.= '<b>Total Financiador '.$financiador.' &nbsp;&nbsp;[Cant: '.$c_financiador.']&nbsp;&nbsp;$'.number_format($total_financiador,2).'</b></td>';
		$reng.= "</tr>";
			
		// Muestro el Total General
		$reng.= "<tr><td style='text-align:right;' colspan='6'>";
		$reng.= '<b>Total General &nbsp;&nbsp;[Cant: '.$c_total.']&nbsp;&nbsp;$'.number_format($total_general,2).'</b></td>';
		$reng.= "</tr>";
		$reng.= '</table>';
		fwrite($fp, $reng);											

		fclose($fp);
		$file = $this->webroot.'temp/'.$nombre_temporal;
		

		header("Content-disposition: attachment; filename=$nombre_temporal");
		header("Content-type: application/xsl");
		readfile($destino);
		unlink($destino);
											
	} // end function exportar1
	
	
	function exportar2($xdesde=0,$xhasta=0) { 	
        $this->layout = '';
		$title = 'Pacientes Atendidos';

		if($xdesde == 0) { $xdesde = time()-(60*60*24*30); }
		if($xhasta == 0) { $xhasta = time();}
		
		$desde = date("Y-m-d H:i:s",$xdesde);
		$hasta = date("Y-m-d H:i:s",$xhasta);
		$cdesde = date("Y-m-d",$xdesde);
		$chasta = date("Y-m-d",$xhasta);

		$client_id = $this->Session->read('ClientesUsuarioLogueado.id');
		$user_id   = $this->Session->read('Auth.User.id');


		$nombre_temporal = 'Pacientes-Atendidos '.$cdesde.' al '.$chasta.'.xls';
		$destino = ROOT.DS.'app'.DS.'webroot'.DS.'temp/'.$nombre_temporal;		

		$fp = fopen("$destino", 'w');

				
		//-----------------------------------------------------------------------------------
		//                                                                        Evoluciones
		//-----------------------------------------------------------------------------------
		$this->Evolution->unbindModel( array('belongsTo' => array('Client', 'Problem') ) );
		$x = $this->Evolution->find('all', array(
												'conditions' => array(
													'Evolution.client_id' => $client_id,
													'Evolution.fecha >= ' => $desde,
													'Evolution.fecha <= ' => $hasta, 
													'Evolution.staterecord_id' => 1) , 
												'order' => array('Evolution.fecha' => 'ASC'),
												));
		$Evoluciones = array();												
		if ($x){
			foreach($x as $z){
				$y['id']          = $z['Evolution']['id'];
				$y['person_id']   = $z['Evolution']['person_id'];
				$y['fecha']       = $z['Evolution']['fecha'];
				$y['paciente']    = $z['Person']['full_name'];
				$y['financiador'] = $z['Person']['financiador'];
				$y['medico_id']   = $z['Evolution']['user_id'];
				$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
				$y['origen']      = 'Evolucion';
							
				$Evoluciones[] = $y;
			}
			unset($x);
		}								


		//-----------------------------------------------------------------------------------
		//                                                                        Problemas
		//-----------------------------------------------------------------------------------
		$this->loadModel('PeopleProblem');
		$this->PeopleProblem->unbindModel( array('belongsTo' => array('Client', 'Problem', 'Stateproblem','Tag','Staterecord') ) );
		$x = $this->PeopleProblem->find('all', array(
									'conditions' => array(
											'PeopleProblem.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
											'PeopleProblem.fecha_origen >= ' => $desde,
											'PeopleProblem.fecha_origen <= ' => $hasta, 
											'PeopleProblem.ficha_id' => 0,
											'PeopleProblem.staterecord_id' => 1
											),
									'order' => array( 'PeopleProblem.fecha_origen' => 'ASC'),
								) );
					
		$Problemas = array();												
		if ($x){
			foreach($x as $z){
				$y['id']          = $z['PeopleProblem']['id'];
				$y['person_id']   = $z['PeopleProblem']['person_id'];
				$y['fecha']       = $z['PeopleProblem']['fecha_origen'];
				$y['paciente']    = $z['Person']['full_name'];
				$y['financiador'] = $z['Person']['financiador'];
				$y['medico_id']   = $z['PeopleProblem']['user_id'];
				$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
				$y['origen']      = 'Problemas';
							
				$Problemas[] = $y;
			}
			unset($x);
		}								

		//-----------------------------------------------------------------------------------
		//                                                                        Practicas
		//-----------------------------------------------------------------------------------
		$this->loadModel('PeoplePractice');
		$this->PeoplePractice->unbindModel( array('belongsTo' => array('Client', 'Practice', 'Staterecord') ) );
		$x = $this->PeoplePractice->find('all', array(
									'conditions' => array(
											'PeoplePractice.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
											'PeoplePractice.fecha >= ' => $desde,
											'PeoplePractice.fecha <= ' => $hasta, 
											'PeoplePractice.ficha_id' => 0,
											'PeoplePractice.staterecord_id' => 1
											),
									'order' => array( 'PeoplePractice.fecha' => 'ASC'),
									'group' => array( 'PeoplePractice.fecha','PeoplePractice.person_id'),																							
								) );
		$Practicas = array();												
		if ($x){
			foreach($x as $z){
				$y['id']          = $z['PeoplePractice']['id'];
				$y['person_id']   = $z['PeoplePractice']['person_id'];
				$y['fecha']       = $z['PeoplePractice']['fecha'];
				$y['paciente']    = $z['Person']['full_name'];
				$y['financiador'] = $z['Person']['financiador'];
				$y['medico_id']   = $z['PeoplePractice']['user_id'];
				$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
				$y['origen']      = 'Practicas';
				
				$Practicas[] = $y;
			}
			unset($x);
		}								
					
		//-----------------------------------------------------------------------------------
		//                                                                    Signos Vitales
		//-----------------------------------------------------------------------------------
		$this->loadModel('PeopleSign');
		$this->PeopleSign->unbindModel( array('belongsTo' => array('Client', 'Sign', 'Staterecord') ) );
		$x = $this->PeopleSign->find('all', array(
									'conditions' => array(
											'PeopleSign.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
											'PeopleSign.fecha >= ' => $desde,
											'PeopleSign.fecha <= ' => $hasta, 
											'PeopleSign.ficha_id' => 0,
											'PeopleSign.staterecord_id' => 1
											),
									'order' => array( 'PeopleSign.fecha' => 'ASC'),
									'group' => array( 'PeopleSign.fecha','PeopleSign.person_id'),
								) );
									
		$Signos = array();												
		if ($x){
			foreach($x as $z){
				$y['id']          = $z['PeopleSign']['id'];
				$y['person_id']   = $z['PeopleSign']['person_id'];
				$y['fecha']       = $z['PeopleSign']['fecha'];
				$y['paciente']    = $z['Person']['full_name'];
				$y['financiador'] = $z['Person']['financiador'];
				$y['medico_id']   = $z['PeopleSign']['user_id'];
				$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
				$y['origen']      = 'SignosVitales';
						
				$Signos[] = $y;
			}
			unset($x);
		}								

		//-----------------------------------------------------------------------------------
		//                                                                    Medicamentos
		//-----------------------------------------------------------------------------------
		$this->loadModel('PeopleVademecum');
		$this->PeopleVademecum->unbindModel( array('belongsTo' => array('Client', 'Vademecum', 'Typeindicacionmedicamento','Staterecord') ) );
		$x = $this->PeopleVademecum->find('all', array(
									'conditions' => array(
											'PeopleVademecum.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
											'PeopleVademecum.created >= ' => $desde,
											'PeopleVademecum.created <= ' => $hasta, 
											'PeopleVademecum.ficha_id' => 0,
											'PeopleVademecum.staterecord_id' => 1
											),
									'order' => array( 'PeopleVademecum.created' => 'ASC'),
								) );
							
		$Medicamentos = array();												
		if ($x){
			foreach($x as $z){
				$y['id']          = $z['PeopleVademecum']['id'];
				$y['person_id']   = $z['PeopleVademecum']['person_id'];
				$y['fecha']       = $z['PeopleVademecum']['created'];
				$y['paciente']    = $z['Person']['full_name'];
				$y['financiador'] = $z['Person']['financiador'];
				$y['medico_id']   = $z['PeopleVademecum']['user_id'];
				$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
				$y['origen']      = 'Medicamentos';
							
				$Medicamentos[] = $y;
			}
			unset($x);
		}								

		//-----------------------------------------------------------------------------------
		//                                                                    Fichas
		//-----------------------------------------------------------------------------------
		$this->loadModel('Ficha');
		$this->Ficha->unbindModel( array('belongsTo' => array('Client', 'Formulario' ) ) );
		$x = $this->Ficha->find('all', array(
									'conditions' => array(
											'Ficha.client_id' => $this->Session->read('ClientesUsuarioLogueado.id'),
											'Ficha.created >= ' => $desde,
											'Ficha.created <= ' => $hasta, 
											'Ficha.staterecord_id' => 1
											),
									'order' => array( 'Ficha.created' => 'ASC'),
								) );
		$Fichas = array();												
		if ($x){
			foreach($x as $z){
				$y['id']          = $z['Ficha']['id'];
				$y['person_id']   = $z['Ficha']['person_id'];
				$y['fecha']       = $z['Ficha']['created'];
				$y['paciente']    = $z['Person']['full_name'];
				$y['financiador'] = $z['Person']['financiador'];
				$y['medico_id']   = $z['Ficha']['user_id'];
				$y['medico']      = $z['User']['lastname'].', '.$z['User']['name'];
				$y['origen']      = 'Fichas';
					
				$Fichas[] = $y;
			}
			unset($x);
		}								
					
					
		//-----------------------------------------------------------------------------------
		//                                                            Arma el array de salida
		//-----------------------------------------------------------------------------------
		$Todos = array_merge($Evoluciones, $Problemas, $Practicas, $Signos, $Medicamentos, $Fichas);
		$Resultado = $this->ordenar_array($Todos, 'fecha', SORT_ASC,'person_id', SORT_ASC); 
		
		
		$reng = '<table cellpadding="0" cellspacing="0" id="myTable" class="tablesorter">
				<thead> 
					<tr>	
						<th>Nro.</th>
						<th>Fecha</th>
						<th>Paciente</th>
						<th>Financiador</th>
						<th>Profesional</th>
					</tr>
				</thead>';
		fwrite($fp, $reng);

		$fecha       = date('d/m/Y', time() );
		$paciente_id = 0;
		$contador = 0;
		
		foreach($Resultado as $r){

			if($fecha == date('d/m/Y', strtotime($r['fecha'])) and $paciente_id ==	$r['person_id'] ) {
				$muestra = 'no';
			} else {
				$muestra = 'si';
				$contador ++;
			}
			$fecha       = date('d/m/Y', strtotime($r['fecha']));
			$paciente_id = $r['person_id'];
			
			if($muestra=='si') { 
				$reng = "<tr>	
							<td>$contador</td>
							<td>".date('d/m/Y', strtotime($r['fecha']))."</td>
							<td>{$r['paciente']}</td>
							<td>{$r['financiador']}</td>
							<td>{$r['medico']}</td>
						</tr>";
				fwrite($fp, $reng);		
			}
		} // end foreach 

		$reng = "</table>";
		fwrite($fp, $reng);
					
		fclose($fp);
		$file = $this->webroot.'temp/'.$nombre_temporal;
		

		header("Content-disposition: attachment; filename=$nombre_temporal");
		header("Content-type: application/xsl");
		readfile($destino);
		unlink($destino);
					
					
	} 


	
	function exportar3($xdesde=0,$xhasta=0) { 	
        $this->layout = '';
		$title = 'Tiempo de Demora';

		if($xdesde == 0) { $xdesde = time()-(60*60*24*30); }
		if($xhasta == 0) { $xhasta = time();}
		
		$desde = date("Y-m-d H:i:s",$xdesde);
		$hasta = date("Y-m-d H:i:s",$xhasta);
		$cdesde = date("Y-m-d",$xdesde);
		$chasta = date("Y-m-d",$xhasta);

		$client_id = $this->Session->read('ClientesUsuarioLogueado.id');
		$user_id   = $this->Session->read('Auth.User.id');

		
		$nombre_temporal = 'Tiempo de demora en la sala de espera.xls';
		$destino = ROOT.DS.'app'.DS.'webroot'.DS.'temp/'.$nombre_temporal;		

		$fp = fopen("$destino", 'w');

		$Resultado = $this->PeopleProduct->query(
			"SELECT  t2.id AS Paciente_id,CONCAT(t2.name, ' ', t2.name2, ' ',t2.lastname, ' ',t2.lastname2, ' ',t2.lastname3) AS Paciente, (YEAR(CURDATE()) - YEAR(t2.fecha_nacimiento)) as Edad, Turn.fecha AS fecha_turno, CONCAT(t3.name, ' ', t3.lastname) AS Profesional, Turn.hora_llegada, Turn.hora_atendido, round((UNIX_TIMESTAMP(Turn.hora_atendido) - UNIX_TIMESTAMP(Turn.hora_llegada))/60) AS min_espera, round((UNIX_TIMESTAMP(Turn.hora_atendido) - UNIX_TIMESTAMP(Turn.fecha))/60) AS min_espera_turno
			FROM
				turns AS Turn
				INNER JOIN people AS t2 ON t2.id = Turn.person_id
				INNER JOIN users AS t3 on t3.id = Turn.doctor_id
			WHERE
				Turn.client_id = '".$this->Session->read('ClientesUsuarioLogueado.id')."' AND Turn.fecha >= '".$desde."' AND Turn.fecha <= '".$hasta."' AND Turn.hora_atendido is not null AND Turn.hora_atendido <> '0000-00-00 00:00:00' 
					AND
					(
						(Turn.hora_llegada is not null AND Turn.hora_llegada <> '0000-00-00 00:00:00') OR
						(Turn.fecha is not null AND Turn.fecha <> '0000-00-00 00:00:00')
					);
			");



		$reng = "<table cellpadding='0' cellspacing='0' id='myTable'>
				<thead> 
					<tr>	
						<th>Paciente</th>
						<th>Edad</th>
						<th>Turno Original</th>
						<th>Espera (min)</th>
						<th>Profesional</th>
					</tr>
				</thead>";
		fwrite($fp, $reng);			


		if(!empty($Resultado)){

			$contador_registros = 0;
			$sum_min_espera = 0;
			$promedio_espera = 0;

			foreach($Resultado as $r){

				if($r['0']['min_espera'] < 1400){ // Si el tiempo de espera es menor a un día, al menos se cargo la hora de llegada
					$min_espera = $r['0']['min_espera'];

					$contador_registros++;
					$sum_min_espera += $r['0']['min_espera'];
					$promedio_espera = round($sum_min_espera / $contador_registros);

				} else { // si no se cargo la hora de llegada, la espera es abismal
					$min_espera = "No fue recepcionado";
				}
			
			
				$reng = "<tr>	
							<td>{$r['0']['Paciente']}</td>
							<td>{$r['0']['Edad']}</td>
							<td>".date('d/m/Y H:i', strtotime($r['Turn']['fecha_turno']))."</td>
							<td>$min_espera</td>
							<td>{$r['0']['Profesional']}</td>
						</tr>";
				fwrite($fp, $reng);					
			} // end foreach 

			$reng = "<tr>
						<td colspan='5'>
							<p style='font-size: 150%;'>Promedio de Espera: $promedio_espera minutos</p>
						</td>
					</tr>";
			fwrite($fp, $reng);					
		}

		$reng = "</table>";
		fwrite($fp, $reng);

		fclose($fp);
		$file = $this->webroot.'temp/'.$nombre_temporal;
		

		header("Content-disposition: attachment; filename=$nombre_temporal");
		header("Content-type: application/xsl");
		readfile($destino);
		unlink($destino);
											
	} // end function exportar3
	
	
	function exportar5($problema_id=0,$xdesde=0,$xhasta=0) { 	
        $this->layout = '';
		$title = 'Tiempo de Demora';

		if($xdesde == 0) { $xdesde = time()-(60*60*24*30); }
		if($xhasta == 0) { $xhasta = time();}
		
		$desde = date("Y-m-d H:i:s",$xdesde);
		$hasta = date("Y-m-d H:i:s",$xhasta);
		$cdesde = date("Y-m-d",$xdesde);
		$chasta = date("Y-m-d",$xhasta);

		$client_id = $this->Session->read('ClientesUsuarioLogueado.id');
		$user_id   = $this->Session->read('Auth.User.id');

		
		$nombre_temporal = "Reporte Problemas Comunes $cdesde al $chasta.xls";
		$destino = ROOT.DS.'app'.DS.'webroot'.DS.'temp/'.$nombre_temporal;		

		$fp = fopen("$destino", 'w');

		
		//-----------------------------------------------------------------------------------
		//                                                  Problemas Atendidos por el medico
		//-----------------------------------------------------------------------------------
		$this->loadModel('PeopleProblem');
		
		$sql = "select pp.id, pp.user_id, pp.problem_id, pp.fecha_origen, pp.person_id, p.name, pe.name, pe.lastname, 
					u.name as med_name, u.lastname as med_lastname
				from people_problems as pp
					left join problems as p on pp.problem_id=p.id
					left join people as pe on pp.person_id=pe.id
					left join users as u on pp.user_id=u.id
				where pp.client_id='$client_id'
					and pp.ficha_id=0 and pp.staterecord_id='1'
					and pp.problem_id = '$problema_id'
					and pp.fecha_origen>='$desde' and pp.fecha_origen<='$hasta'
				order by pp.fecha_origen DESC";
					
		$Resultado = $this->PeopleProblem->query( $sql);

	
		$reng = "<table cellpadding='0' cellspacing='0' >
				<thead> 
					<tr>	
						<th>Nro.</th>
						<th>Fecha</th>
						<th>Paciente</th>
						<th>Profesional</th>
						<th>Problema</th>
					</tr>
				</thead>";
		fwrite($fp, $reng);		

		$contador = 0;
		
		foreach($Resultado as $r){
		
			$contador ++;
			$fecha       = date('d/m/Y', strtotime($r['pp']['fecha_origen']));
			$paciente    = $r['pe']['name'].' '.$r['pe']['lastname'];
			$paciente_id = $r['pp']['person_id'];
			$medico      = $r['u']['med_name'].' '.$r['u']['med_lastname'];
			$problema    = $r['p']['name'];
			$reng = "<tr>	
						<td>$contador</td>
						<td>$fecha</td>
						<td>$paciente</td>
						<td>$medico</td>
						<td>$problema</td>
					</tr>";
			fwrite($fp, $reng);				
		} // end foreach 

	
		$reng = "</table>";
		fwrite($fp, $reng);

		fclose($fp);
		$file = $this->webroot.'temp/'.$nombre_temporal;
		

		header("Content-disposition: attachment; filename=$nombre_temporal");
		header("Content-type: application/xsl");
		readfile($destino);
		unlink($destino);
											
	} // end function exportar5



}
?>